package flights.db;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import flights.bi.Airline;

public class AirlineHandler {

	public static Airline getAirlineById(String id) throws SQLException
	{
		Airline airline = null;
		Connection conn = DBConn.createConnection();
		String sql = "SELECT * FROM airline WHERE airline_id=?";
		
		try (PreparedStatement st = conn.prepareStatement(sql)) {
			st.clearParameters();
			st.setString(1,id);
			ResultSet rs = st.executeQuery();
			airline = extractAirlineFromRS(rs);
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		
		conn.close();
		return airline;
	}
	
	public static Airline delAirlineById(String id) throws SQLException
	{
		Airline airline = null;
		Connection conn = DBConn.createConnection();
		String sql = "SELECT * FROM airline WHERE airline_id=?";
		String sql_del  = "DELETE FROM airline WHERE airline_id=?";
		PreparedStatement st = conn.prepareStatement(sql);
		st.clearParameters();
		st.setString(1,id);
		ResultSet rs = st.executeQuery();
		airline = extractAirlineFromRS(rs);
		
		PreparedStatement st_new = conn.prepareStatement(sql_del);
		st_new.clearParameters();
		st_new.setString(1,id);
		st_new.executeUpdate();
		
		conn.close();
		return airline;
	}
	
	public static List<Airline> getAll() throws SQLException
	{
		List<Airline> airlines = null;
		Connection conn = DBConn.createConnection();
		String sql = "SELECT * FROM airline";
		
		try (PreparedStatement st = conn.prepareStatement(sql)) {
			st.clearParameters();
			ResultSet rs = st.executeQuery();
			airlines = extractAllAirlineFromRS(rs);
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		
		conn.close();
		return airlines;
	}
	
	private static List<Airline> extractAllAirlineFromRS(ResultSet ResultSet) throws SQLException {
		List<Airline> airlines = new ArrayList<Airline>();
		Airline airline = null;
		int nRowCount = 0;
		while (ResultSet.next()) {
			int airline_id = ResultSet.getInt("airline_id");
			String logon = ResultSet.getString("logon");
			String password = ResultSet.getString("password");
			String name = ResultSet.getString("name");
			Date   registration_date = ResultSet.getDate("registration_date");
			airline = new Airline(airline_id, logon, password, name, registration_date);
			airlines.add(airline);
			nRowCount++;
		}
		
		if (nRowCount == 0)
		{
			return null;
		}
		
		return airlines;
	}
	
	private static Airline extractAirlineFromRS(ResultSet ResultSet) throws SQLException {
		Airline airline = null;
		int nRowCount = 0;
		while (ResultSet.next()) {
			int airline_id = ResultSet.getInt("airline_id");
			String logon = ResultSet.getString("logon");
			String password = ResultSet.getString("password");
			String name = ResultSet.getString("name");
			Date   registration_date = ResultSet.getDate("registration_date");
			airline = new Airline(airline_id, logon, password, name, registration_date);
			nRowCount++;
		}
		
		if (nRowCount != 1)
		{
			return null;
		}
		
		return airline;
	}
	
	public static int upsertAirline(List<Airline> Airlines) throws SQLException
	{
		Connection conn = DBConn.createConnection();
		ResultSet generatedKeys = null;
		int nRowsAffected = 0;
		for (int i = 0; i < Airlines.size(); i++)
		{
			
			Airline currAir = Airlines.get(i);
			if (getAirlineById("" + currAir.getAirline_id()) != null)
			{
				String sql = "update airline set logon=? , password=? , name=?, registration_date=? where airline_id = ?";
				try (PreparedStatement st = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS)) {
					st.clearParameters();
					nRowsAffected += st.executeUpdate();
				} catch (SQLException ex) {
					System.err.println(ex.getMessage());
					throw new SQLException(ex.getMessage());
				}
			}
			else // new Flight_Seat
			{
				String sql = "INSERT INTO airline (`airline_id`, `logon`, `password`, `name`, `registration_date`) VALUES (?, ?, ?, ?, ?)";
				//String sql = "insert into flight_seat(Flight_Id,Class,`row`,`column`,Cost,Reserved) values (?,?,?,?,?,?)";
				try (PreparedStatement st = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
					st.clearParameters();
					st.setInt(1, currAir.getAirline_id());
					st.setString(2, currAir.getLogon());
					st.setString(3, currAir.getPassword());
					st.setString(4, currAir.getName());
					st.setDate(5, currAir.getRegistration_date());
					nRowsAffected += st.executeUpdate();
					generatedKeys = st.getGeneratedKeys();
					if (generatedKeys.next()) {
			            nRowsAffected = generatedKeys.getInt(1);
			        } else {
			            throw new SQLException("Creating user failed, no generated key obtained.");
			        }
					
				} catch (SQLException ex) {
					System.err.println(ex.getMessage());
				}
			}
		}
		
		return nRowsAffected;
	}

	public static Airline getAirlineByCredentials(String logon, String password) throws SQLException 
	{
		Airline air = null;
		Connection conn = DBConn.createConnection();
		String sql = "SELECT * FROM airline WHERE logon=? and password=?";
		
		try (PreparedStatement st = conn.prepareStatement(sql)) {
			st.clearParameters();
			st.setString(1,logon);
			st.setString(2,password);
			ResultSet rs = st.executeQuery();
			air = extractAirlineFromRS(rs);
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		
		conn.close();
		return air;
	}
}